﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Common.DBUtility;

namespace lib.DAL
{
    public abstract class SQL_DAL_Base
    {
        /// <summary>
        /// 可通过重载此方法实现初始化时的动作
        /// </summary>
        protected virtual void InstanceInited()
        {
        }

        /// <summary>
        /// 表或视图名称
        /// </summary>
        protected string tableOrViewName = string.Empty;
        /// <summary>
        /// 表或视图名称
        /// </summary>
        protected string TableOrViewName
        {
            get { return tableOrViewName; }
        }

        protected string primeryKeyNames = string.Empty;
        /// <summary>
        /// 定义主键
        /// </summary>
        protected string PrimeryKeyNames
        {
            get { return primeryKeyNames; }
        }

        /// <summary>
        /// 使用事务，根据条件更新对应的数据表记录，可指定部分行，返回受影响的行数
        /// 此方法为自动生成，请不要手动修改
        /// </summary>
        /// <param name="trans">使用的事务,可以为null</param>
        /// <param name="updateSql">指定更新SQL(不含set)</param>
        /// <param name="conditionSql">要更新的记录对象</param>
        /// <param name="parms">参数</param>
        /// <returns>受影响的行数</returns>
        public virtual int Update(SqlTransaction trans, string updateSql, string conditionSql, params SqlParameter[] parms)
        {
            if (updateSql.Trim() == "" || conditionSql.Trim() == "") throw new Exception("请指定更新细节和条件");
            if (trans == null) return SqlHelperWebDAL.ExecuteNonQuery(CommandType.Text, "update " + TableOrViewName + " set " + updateSql + " where " + conditionSql, parms);
            else return SqlHelperWebDAL.ExecuteNonQuery(trans, CommandType.Text, "update " + TableOrViewName + " set " + updateSql + " where " + conditionSql, parms);
        }
        /// <summary>
        /// 使用事务，根据条件删除该实体对应的数据表记录，返回受影响的行数
        /// 此方法为自动生成，请不要手动修改
        /// </summary>
        /// <param name="conditionSql">删除条件，如a=@a </param>
        /// <param name="trans">使用的事务,可以为null</param>
        /// <param name="parms">SQL参数</param>
        /// <returns>受影响的行数</returns>
        public virtual int Delete(string conditionSql, SqlTransaction trans, params SqlParameter[] parms)
        {
            if (conditionSql.Trim() == "") throw new Exception("禁止此删除方式");
            if (trans == null) return SqlHelperWebDAL.ExecuteNonQuery(CommandType.Text, "delete " + TableOrViewName + " where " + conditionSql, parms);
            else return SqlHelperWebDAL.ExecuteNonQuery(trans, CommandType.Text, "delete " + TableOrViewName + " where " + conditionSql, parms);
        }
        /// <summary>
        /// 统计符合条件的记录数
        /// </summary>
        /// <param name="conditionSql">条件SQL(不含where)</param>
        /// <param name="parms">SQL参数</param>
        /// <returns></returns>
        public virtual int GetListCount(string conditionSql, params SqlParameter[] parms)
        {
            return GetListCount(null, conditionSql, null, parms);
        }
        /// <summary>
        /// 统计符合条件的记录数
        /// </summary>
        /// <param name="conditionSql">条件SQL(不含where)</param>
        /// <param name="countCol">指定统计列（可用Distinct关键字）</param>
        /// <param name="parms">SQL参数</param>
        /// <returns></returns>
        public virtual int GetListCount(string conditionSql, string countCol, params SqlParameter[] parms)
        {
            return GetListCount(null, conditionSql, countCol, parms);
        }
        /// <summary>
        /// 统计符合条件的记录数，使用事务
        /// </summary>
        /// <param name="trans">事务(可为null)</param>
        /// <param name="conditionSql">条件SQL(不含where)</param>
        /// <param name="parms">SQL参数</param>
        /// <returns></returns>
        public virtual int GetListCount(SqlTransaction trans, string conditionSql, params SqlParameter[] parms)
        {
            return GetListCount(trans, conditionSql, null, parms);
        }
        /// <summary>
        /// 统计符合条件的记录数，使用事务
        /// </summary>
        /// <param name="trans">事务(可为null)</param>
        /// <param name="conditionSql">条件SQL(不含where)</param>
        /// <param name="countCol">指定统计列（可用Distinct关键字）</param>
        /// <param name="parms">SQL参数</param>
        /// <returns></returns>
        public virtual int GetListCount(SqlTransaction trans, string conditionSql, string countCol, params SqlParameter[] parms)
        {
            object obj = null;
            if (trans != null) obj = SqlHelperWebDAL.ExecuteScalar(trans, CommandType.Text, "select isnull(count(" + (string.IsNullOrEmpty(countCol) ? "*" : countCol) + "),0) from " + TableOrViewName + " " + ((conditionSql.Trim() == "") ? "" : ("where " + conditionSql)), parms);
            else obj = SqlHelperWebDAL.ExecuteScalar(CommandType.Text, "select isnull(count(" + (string.IsNullOrEmpty(countCol) ? "*" : countCol) + "),0) from " + TableOrViewName + " " + ((conditionSql.Trim() == "") ? "" : ("where " + conditionSql)), parms);
            return int.Parse(obj.ToString());
        }
        /// <summary>
        /// 返回符合条件的所有记录，可指定返回字段
        /// </summary>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetList(string conditionSql, string selectFields, string orderBy, params SqlParameter[] parms)
        {
            return GetList(conditionSql, selectFields, orderBy, null, parms);
        }
        /// <summary>
        /// 返回符合条件的所有记录，可指定返回字段
        /// </summary>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="trans">事务(可为null)</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetList(string conditionSql, string selectFields, string orderBy, SqlTransaction trans, params SqlParameter[] parms)
        {
            string sql = SqlHelper.GetPageSQL(TableOrViewName, PrimeryKeyNames, conditionSql, selectFields, orderBy, 10000000, 1);
            DataSet ds = SqlHelperWebDAL.GetDataSet(trans, CommandType.Text, sql, parms);
            return ds.Tables[0];
        }

        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2005及以上可用
        /// </summary>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetList(ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            return GetListMSSQL2005(ref totalCount, conditionSql, selectFields, orderBy, pageSize, pageIndex, parms);
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2005及以上可用
        /// </summary>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual SqlDataReader GetList(string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            return GetListMSSQL2005(conditionSql, selectFields, orderBy, pageSize, pageIndex, parms);
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2005及以上可用
        /// </summary>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="pageRecordOffset">分页时的偏移值（前面pageRecordOffset条使用其他方式或其他条件取出，小于0则表示最前面pageRecordOffset条记录被忽略）</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetList(ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, int pageRecordOffset, params SqlParameter[] parms)
        {
            return GetListMSSQL2005(ref totalCount, conditionSql, selectFields, orderBy, pageSize, pageIndex, pageRecordOffset, parms);
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2005及以上可用
        /// </summary>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="pageRecordOffset">分页时的偏移值（前面pageRecordOffset条使用其他方式或其他条件取出，小于0则表示最前面pageRecordOffset条记录被忽略）</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual SqlDataReader GetList(string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, int pageRecordOffset, params SqlParameter[] parms)
        {
            return GetListMSSQL2005(conditionSql, selectFields, orderBy, pageSize, pageIndex, pageRecordOffset, parms);
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2005及以上可用
        /// </summary>
        /// <param name="joinSql">join语句，如inner Join B on 当前表名.字段A=B.字段A</param>
        /// <param name="throwSql">将SQL语句作为异常抛出，用于检测语句正确性</param>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetList_JOIN(string joinSql, bool throwSql, ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            return GetList_JOIN(joinSql, throwSql, ref totalCount, conditionSql, selectFields, orderBy, pageSize, pageIndex, 0, parms);
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2005及以上可用
        /// </summary>
        /// <param name="joinSql">join语句，如inner Join B on 当前表名.字段A=B.字段A</param>
        /// <param name="throwSql">将SQL语句作为异常抛出，用于检测语句正确性</param>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="pageRecordOffset">分页时的偏移值（前面pageRecordOffset条使用其他方式或其他条件取出，小于0则表示最前面pageRecordOffset条记录被忽略）</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetList_JOIN(string joinSql, bool throwSql, ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, int pageRecordOffset, params SqlParameter[] parms)
        {
            return GetListMSSQL2005_JOIN(joinSql, throwSql, ref totalCount, conditionSql, selectFields, orderBy, pageSize, pageIndex, pageRecordOffset, parms);
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// 注意：仅MSSQL2005及以上可用
        /// </summary>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetListMSSQL2005(ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            return GetListMSSQL2005(ref totalCount, conditionSql, selectFields, orderBy, pageSize, pageIndex, 0, parms);
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// 注意：仅MSSQL2005及以上可用
        /// </summary>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="pageRecordOffset">分页时的偏移值（前面pageRecordOffset条使用其他方式或其他条件取出，小于0则表示最前面pageRecordOffset条记录被忽略）</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetListMSSQL2005(ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, int pageRecordOffset, params SqlParameter[] parms)
        {
            string sql = SqlHelper.GetPageSQL_MSSQL2005(TableOrViewName, PrimeryKeyNames, conditionSql, selectFields, orderBy, pageSize, pageIndex, pageRecordOffset);
            if (totalCount <= 0) sql += ";select isnull(count(*),0) from " + TableOrViewName + " " + ((conditionSql.Trim() == "") ? "" : ("where " + conditionSql));
            DataSet ds = SqlHelperWebDAL.GetDataSet(CommandType.Text, sql, parms);
            if (totalCount <= 0) totalCount = int.Parse(ds.Tables[1].Rows[0][0].ToString());
            totalCount += pageRecordOffset;  //修正总记录数以便分页
            return ds.Tables[0];
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2005及以上可用
        /// </summary>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual SqlDataReader GetListMSSQL2005(string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            string sql = SqlHelper.GetPageSQL_MSSQL2005(TableOrViewName, PrimeryKeyNames, conditionSql, selectFields, orderBy, pageSize, pageIndex);
            return SqlHelperWebDAL.ExecuteReader(CommandType.Text, sql, parms);
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2005及以上可用
        /// </summary>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="pageRecordOffset">分页时的偏移值（前面pageRecordOffset条使用其他方式或其他条件取出，小于0则表示最前面pageRecordOffset条记录被忽略）</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual SqlDataReader GetListMSSQL2005(string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, int pageRecordOffset, params SqlParameter[] parms)
        {
            string sql = SqlHelper.GetPageSQL_MSSQL2005(TableOrViewName, PrimeryKeyNames, conditionSql, selectFields, orderBy, pageSize, pageIndex, pageRecordOffset);
            return SqlHelperWebDAL.ExecuteReader(CommandType.Text, sql, parms);
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// 注意：仅MSSQL2005及以上可用
        /// </summary>
        /// <param name="joinSql">join语句，如inner Join B on 当前表名.字段A=B.字段A</param>
        /// <param name="throwSql">将SQL语句作为异常抛出，用于检测语句正确性</param>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetListMSSQL2005_JOIN(string joinSql, bool throwSql, ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            return GetListMSSQL2005_JOIN(joinSql, throwSql, ref totalCount, conditionSql, selectFields, orderBy, pageSize, pageIndex, 0, parms);
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// 注意：仅MSSQL2005及以上可用
        /// </summary>
        /// <param name="joinSql">join语句，如inner Join B on 当前表名.字段A=B.字段A</param>
        /// <param name="throwSql">将SQL语句作为异常抛出，用于检测语句正确性</param>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="pageRecordOffset">分页时的偏移值（前面pageRecordOffset条使用其他方式或其他条件取出，小于0则表示最前面pageRecordOffset条记录被忽略）</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetListMSSQL2005_JOIN(string joinSql, bool throwSql, ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, int pageRecordOffset, params SqlParameter[] parms)
        {
            if (orderBy.Trim() == "") orderBy = PrimeryKeyNames;
            string sql = SqlHelper.GetPageSQL_MSSQL2005_JOIN(TableOrViewName + " " + joinSql + "", PrimeryKeyNames, conditionSql, selectFields, orderBy, pageSize, pageIndex, pageRecordOffset);
            if (totalCount <= 0) sql += ";select isnull(count(*),0) from " + TableOrViewName + " " + joinSql + ((conditionSql.Trim() == "") ? "" : (" where " + conditionSql));
            if (throwSql) throw new Exception(sql);
            DataSet ds = SqlHelperWebDAL.GetDataSet(CommandType.Text, sql, parms);
            if (totalCount <= 0) totalCount = int.Parse(ds.Tables[1].Rows[0][0].ToString());
            return ds.Tables[0];
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2000及以上可用
        /// </summary>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetListMSSQL2000(ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            string sql = SqlHelper.GetPageSQL(TableOrViewName, PrimeryKeyNames, conditionSql, selectFields, orderBy, pageSize, pageIndex);
            if (totalCount <= 0) sql += ";select isnull(count(*),0) from " + TableOrViewName + ((conditionSql.Trim() == "") ? "" : (" where " + conditionSql));
            DataSet ds = SqlHelperWebDAL.GetDataSet(CommandType.Text, sql, parms);
            if (totalCount <= 0) totalCount = int.Parse(ds.Tables[1].Rows[0][0].ToString());
            return ds.Tables[0];
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2000及以上可用
        /// </summary>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual SqlDataReader GetListMSSQL2000(string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            string sql = SqlHelper.GetPageSQL(TableOrViewName, PrimeryKeyNames, conditionSql, selectFields, orderBy, pageSize, pageIndex);
            return SqlHelperWebDAL.ExecuteReader(CommandType.Text, sql, parms);
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2000及以上可用
        /// </summary>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="pageRecordOffset">分页时的偏移值（前面pageRecordOffset条使用其他方式或其他条件取出，小于0则表示最前面pageRecordOffset条记录被忽略）</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetListMSSQL2000(ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, int pageRecordOffset, params SqlParameter[] parms)
        {
            string sql = SqlHelper.GetPageSQL(TableOrViewName, PrimeryKeyNames, conditionSql, selectFields, orderBy, pageSize, pageIndex, pageRecordOffset);
            if (totalCount <= 0) sql += ";select isnull(count(*),0) from " + TableOrViewName + ((conditionSql.Trim() == "") ? "" : (" where " + conditionSql));
            DataSet ds = SqlHelperWebDAL.GetDataSet(CommandType.Text, sql, parms);
            if (totalCount <= 0) totalCount = int.Parse(ds.Tables[1].Rows[0][0].ToString());
            return ds.Tables[0];
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2000及以上可用
        /// </summary>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="pageRecordOffset">分页时的偏移值（前面pageRecordOffset条使用其他方式或其他条件取出，小于0则表示最前面pageRecordOffset条记录被忽略）</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual SqlDataReader GetListMSSQL2000(string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, int pageRecordOffset, params SqlParameter[] parms)
        {
            string sql = SqlHelper.GetPageSQL(TableOrViewName, PrimeryKeyNames, conditionSql, selectFields, orderBy, pageSize, pageIndex, pageRecordOffset);
            return SqlHelperWebDAL.ExecuteReader(CommandType.Text, sql, parms);
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2000及以上可用
        /// </summary>
        /// <param name="joinSql">join语句，如inner Join B on LS_Users.fdUserId=B.fdUserId</param>
        /// <param name="throwSql">将SQL语句作为异常抛出，用于检测语句正确性</param>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetListMSSQL2000_JOIN(string joinSql, bool throwSql, ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, params SqlParameter[] parms)
        {
            return GetListMSSQL2000_JOIN(joinSql, throwSql, ref totalCount, conditionSql, selectFields, orderBy, pageSize, pageIndex, 0, parms);
        }
        /// <summary>
        /// 分页返回符合条件的记录，可指定返回字段
        /// MSSQL2000及以上可用
        /// </summary>
        /// <param name="joinSql">join语句，如inner Join B on LS_Users.fdUserId=B.fdUserId</param>
        /// <param name="throwSql">将SQL语句作为异常抛出，用于检测语句正确性</param>
        /// <param name="totalCount">返回所有记录数,如果输入大于0,则不重新计算</param>
        /// <param name="conditionSql">指定条件(不含Where)</param>
        /// <param name="selectFields">要返回的字段，为空则返回所有字段</param>
        /// <param name="orderBy">排序方式(不包含order by)</param>
        /// <param name="pageSize">分页大小--每页包含的记录数</param>
        /// <param name="pageIndex">页码--要获取第几页的记录</param>
        /// <param name="pageRecordOffset">分页时的偏移值（前面pageRecordOffset条使用其他方式或其他条件取出，小于0则表示最前面pageRecordOffset条记录被忽略）</param>
        /// <param name="parms">执行SQL语句使用的参数</param>
        /// <returns>按分页大小返回指定页码的记录的指定字段</returns>
        public virtual DataTable GetListMSSQL2000_JOIN(string joinSql, bool throwSql, ref int totalCount, string conditionSql, string selectFields, string orderBy, int pageSize, int pageIndex, int pageRecordOffset, params SqlParameter[] parms)
        {
            string sql = SqlHelper.GetPageSQL(TableOrViewName + " " + joinSql, PrimeryKeyNames, conditionSql, selectFields, orderBy, pageSize, pageIndex);
            if (totalCount <= 0) sql += ";select isnull(count(*),0) from " + TableOrViewName + " " + joinSql + " " + ((conditionSql.Trim() == "") ? "" : ("where " + conditionSql));
            if (throwSql) throw new Exception(sql);
            DataSet ds = SqlHelperWebDAL.GetDataSet(CommandType.Text, sql, parms);
            if (totalCount <= 0) totalCount = int.Parse(ds.Tables[1].Rows[0][0].ToString());
            if (pageRecordOffset > 0) totalCount += pageRecordOffset;
            return ds.Tables[0];
        }
    }
}
